<?php
class  mysql_pdo
{
    public $setting;
    public $pdo;
    public function __construct()
    {
        global $config;
        //        $setting = ['dsn'=>'mysql:dbname=fenfa;host=127.0.0.1','user'=>'homestead','password'=>'secret'];
        $setting = [
            'dsn' => 'mysql:dbname=' . $config['dbname'] . ';host=' . $config['host'],
            'user' => $config['user'],
            'password' => $config['password'],
        ];

        $this->setting = $setting;
        //链接数据库
        try {
            $this->pdo_params = [
                PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8", //设置编码
                PDO::ATTR_EMULATE_PREPARES   => true, //使用预处理
            ];
            $this->pdo = new PDO($this->setting['dsn'], $this->setting['user'], $this->setting['password'], $this->pdo_params);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            file_put_contents("../api_resource/logs/" . date("Ymd") . "_queue_ll.log",  "[" . date("Y-m-d H:i:s") . "]" . "链接数据库异常----" . $e->getMessage() . PHP_EOL, FILE_APPEND);
            echo $e->getMessage();
            exit;
        }
    }

    // 获取数据库中的一行
    function db_getRow($sql, $param = [])
    {
        $sth = $this->pdo->prepare($sql);
        $sth->execute($param);
        $res2 = $sth->fetch(PDO::FETCH_ASSOC);
        $sth = null; //释放结果集
        return $res2;
    }

    //获取数据库中的一行一列
    function db_getOne($sql, $param = [])
    {
        //取一列
        $sth = $this->pdo->prepare($sql);
        $sth->execute($param);
        $res3 = $sth->fetchColumn(0);
        $sth = null; //释放结果集
        return $res3;
    }

    //获取数据库中的一列
    function db_getCol($sql, $param = [])
    {
        //取一列
        $sth = $this->pdo->prepare($sql);
        // var_dump('params');
        // var_dump($param);
        $sth->execute($param);
        $res3 = $sth->fetchAll(PDO::FETCH_COLUMN, 0);
        $sth = null; //释放结果集
        return $res3;
    }

    // 获取多行
    function db_getAll($sql, $param = [])
    {
        $sth = $this->pdo->prepare($sql);
        $sth->execute($param);
        $res1 = $sth->fetchAll(PDO::FETCH_ASSOC); //有多种方式可以设置返回的值
        $sth = null; //释放结果集
        return $res1;
    }

    /**
     * 将数据库中查出的列表以指定的 id 作为数组的键名
     */
    function convert_arr_key($arr, $key_name)
    {
        $arr2 = array();
        foreach ($arr as $key => $val) {
            $arr2[$val[$key_name]] = $val;
        }
        return $arr2;
    }

    /** 写入语句*/
    function db_query($sql, $param = [])
    {
        try {
            $sth = $this->pdo->prepare($sql);
            $sth->execute($param);
        } catch (Exception $e) {
            file_put_contents("../api_resource/logs/" . date("Ymd") . "_queue_ll.log",  "[" . date("Y-m-d H:i:s") . "]" . "数据库写入失败----" . $e->getMessage() . ',sql:' . $sql . PHP_EOL, FILE_APPEND);
        }

        if (strstr(strtolower($sql), 'insert')) {
            $sth = null; //释放结果集
            return $this->pdo->lastInsertId();
        } else {
            $rowCount = $sth->rowCount();
            $sth = null; //释放结果集
            return $rowCount;
        }
    }
    // 缓存
    function cache($key, $value = '', $expires_time = 60)
    {
        $time = time();
        if (trim($value)) {
            $this->db_query("delete from cache where `key`='$key' limit 1");
            $expires_time = $time + $expires_time;
            $this->db_query("insert ignore into cache set `key`='$key',`value`='$value',expires_time=$expires_time");
        } else {
            $row = $this->db_getRow("select * from cache where `key`='$key' limit 1");
            if ($time > $row['expires_time']) {
                $this->db_query("delete from cache where `key`='$key' limit 1");
                return null;
            } else
                return     $row['value'];
        }
    }
}


//查询多条数据
/*
$sth = $this->pdo->prepare('SELECT * FROM article WHERE id >=:id');
$sth->execute([':id'=>1]);
$res1 = $sth->fetchAll(PDO::FETCH_ASSOC); //有多种方式可以设置返回的值
print_r($res1);
exit;
*/
/*
//查询一条数据
$sth = $this->pdo->prepare('SELECT * FROM article WHERE id =:id');
$sth->execute([':id'=>1]);
$res2 = $sth->fetch(PDO::FETCH_ASSOC);
print_r($res2);
$sth = null; //释放结果集
*/

/*
//取一列
$sth = $this->pdo->prepare('SELECT title FROM article WHERE 1');
$sth->execute();
$res3 = $sth->fetchAll(PDO::FETCH_COLUMN);
print_r($res3);
$sth = null; //释放结果集
exit;
*/

//添加
/*
$sth = $this->pdo->prepare("INSERT INTO `article`(`title`,`content`,`created_at`)VALUES(:title,:content,:created_at)");
$sth->execute([
    ':title'=>'标题',
    ':content'=>'内容',
    ':created_at'=>time()
]);
echo "受影响的行数:", $sth->rowCount(), "<br/>";
echo "自增ID：", $this->pdo->lastInsertId(), "<br/>";
*/

//修改
/*
$sth = $this->pdo->prepare("UPDATE `article` SET `title`=:title WHERE `id`=:id");
$sth->execute([
    ':title'=>'标题12',
    ':id'=>2
]);
echo "受影响的行数:", $sth->rowCount(), "<br/>";
exit;
*/
/*
//删除
$sth = $this->pdo->prepare("DELETE FROM `article` WHERE `id`=:id");
$sth->execute([':id'=>6]);
echo "受影响的行数:", $sth->rowCount(), "<br/>";
*/
//事务
/*
try {
    $this->pdo->beginTransaction();

    $sth = $this->pdo->prepare("UPDATE `article` SET `title`=:title WHERE `id`=:id");
    $sth->execute([':title'=>'测试事物', ':id'=>1]);
    echo "受影响的行数:", $sth->rowCount(), "<br/>";

    $sth = $this->pdo->prepare("UPDATE `article` SET `title`=:title WHERE `id`=:id");
    $sth->execute([':title'=>'测试事物2', ':id'=>2]);
    echo "受影响的行数:", $sth->rowCount(), "<br/>";

    //有时候，你可能需要手动抛出异常，来进行回滚操作
    // throw new Exception("错误信息", 1);

    $this->pdo->commit();
    echo "成功!";
} catch (Exception $e) {
    $this->pdo->rollBack();
    echo $e->getMessage();
}

$this->pdo = null; //关闭数据库连接
*/
/*
 CREATE TABLE `article` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8mb4_unicode_ci,
  `created_at` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test`.`article` (`id`, `title`, `content`, `created_at`) VALUES ('1', '标题12', '测试内容', '234');
INSERT INTO `test`.`article` (`id`, `title`, `content`, `created_at`) VALUES ('2', '标题12', '测试内容2', '2323');

*/
